Setting Excel ChartTitle from DXL

Does anyone know if it is possible to set ChartTitle.Text  from DXL.

I want to pass a string variable from dxl code into a graph within an ActiveWorkBook.

I am having difficulty in getting a handle of a property using oleGet or oleMethod to retrun an object which I can set and pass back to Excel.

 

Many thanks in advance.


Tobermory - Sun Aug 27 08:34:55 EDT 2017

Re: Setting Excel ChartTitle from DXL
Mathias Mamsch - Tue Aug 29 03:12:20 EDT 2017

Normally these problems can be solved, by applying the correct calls (oleGet vs. oleMethod) and there are some problem with named arguments. 

If you can come up with a working vba it is much easier to help you, because then the question of finding the specific COM calls is gone any we only need to translate to DXL. 

And maybe if you have a DXL prepared, that will already open the workbook, etc. it will be even better.

Regards, Mathias

Re: Setting Excel ChartTitle from DXL
O.Wilkop - Tue Aug 29 04:31:59 EDT 2017

Here's a quick example on how to open a exel file (C:/chart.xlsx), find the chart called "Diagramm 1" on the first sheet of the workbook and change its title to "New Title". This is just a quick example without any error checks or cleanup, so if your chart.xlsx doesn't exist or you don't have a chart called "Diagramm 1" in it the script will obviously fail.

  

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application");

OleAutoObj objWorkbooks;
OleAutoArgs args = create();
olePut(objExcel, "Visible", true);
oleGet(objExcel, "Workbooks", objWorkbooks);


OleAutoObj objWorkbook;
put(args, "C:/chart.xlsx");
oleMethod(objWorkbooks, "Open", args, objWorkbook);


OleAutoObj objSheet;
clear args;
put(args, 1);
oleGet(objWorkbook, "Sheets", args, objSheet);


OleAutoObj objChart;
clear args;
put(args, "Diagramm 1");
oleMethod(objSheet, "ChartObjects", args, objChart);


OleAutoObj objChart2;
oleGet(objChart, "Chart", objChart2);


OleAutoObj objChartTitle;
oleGet(objChart2, "ChartTitle", objChartTitle);
olePut(objChartTitle, "Text", "New Title");

 

 

It should give you enough of an understanding on how to accomplish what you want to do though!

The dxl above is roughly equal to the following excel macro: 

Set objWorkbook = Workbooks.Open("C:/chart.xlsx")
Set objSheet = objWorkbook.Sheets(1)
Set objChart = objSheet.ChartObjects("Diagramm 1")
objChart.Chart.ChartTitle.Text = "New Title"

 

Re: Setting Excel ChartTitle from DXL
Tobermory - Thu Aug 31 04:50:24 EDT 2017

O.Wilkop - Tue Aug 29 04:31:59 EDT 2017

Here's a quick example on how to open a exel file (C:/chart.xlsx), find the chart called "Diagramm 1" on the first sheet of the workbook and change its title to "New Title". This is just a quick example without any error checks or cleanup, so if your chart.xlsx doesn't exist or you don't have a chart called "Diagramm 1" in it the script will obviously fail.

  

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application");

OleAutoObj objWorkbooks;
OleAutoArgs args = create();
olePut(objExcel, "Visible", true);
oleGet(objExcel, "Workbooks", objWorkbooks);


OleAutoObj objWorkbook;
put(args, "C:/chart.xlsx");
oleMethod(objWorkbooks, "Open", args, objWorkbook);


OleAutoObj objSheet;
clear args;
put(args, 1);
oleGet(objWorkbook, "Sheets", args, objSheet);


OleAutoObj objChart;
clear args;
put(args, "Diagramm 1");
oleMethod(objSheet, "ChartObjects", args, objChart);


OleAutoObj objChart2;
oleGet(objChart, "Chart", objChart2);


OleAutoObj objChartTitle;
oleGet(objChart2, "ChartTitle", objChartTitle);
olePut(objChartTitle, "Text", "New Title");

 

 

It should give you enough of an understanding on how to accomplish what you want to do though!

The dxl above is roughly equal to the following excel macro: 

Set objWorkbook = Workbooks.Open("C:/chart.xlsx")
Set objSheet = objWorkbook.Sheets(1)
Set objChart = objSheet.ChartObjects("Diagramm 1")
objChart.Chart.ChartTitle.Text = "New Title"

 

Thanks for the code sample. I've tried running  the code sample above and the errors indicated by the comments occur.

OleAutoObj objChart;

clear args;

put(args, "Diagramm 1");

oleMethod(objSheet, "ChartObjects", args, objChart);  // returns null objChart

 

OleAutoObj objChart2;

oleGet(objChart, "Chart", objChart2);  // objChart - null object passed

 

I can't get a handle for the ChartObject within the collection so that the chart properties can be set.

 

Also, if we get passed that error would there need to be an additional line as suggested below:

 

OleAutoObj objChartTitle;


oleGet(objChart2, "ChartTitle", objChartTitle);


olePut(objChartTitle, "HasTitle", 1);  //set HasTitle property to True


olePut(objChartTitle, "Text", "New Title");

 

 

Re: Setting Excel ChartTitle from DXL
O.Wilkop - Thu Aug 31 05:08:01 EDT 2017

My example assumed that you already have a charttitle and just want to change it, if you want to add a charttitle you have to add the following before retrieving the ChartTitle object:

  

bool hasTitle = false;
oleGet(objChart2, "HasTitle", hasTitle);

if(!hasTitle){
        clear args;
        put(args, 1);
        oleMethod(objChart2, "SetElement", args);
}

 

the "1" in args means "ChartTitleCenteredOverlay", a "2" would be "ChartTitleAboveChart", "0" would be "ChartTitleNone".